<?php

/*
 * Copyright (C) 2006-2009 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * The object used for executing a static SQL statement and obtaining the results produced by it
 *
 * Only one SpicaResultSet object per SpicaStatement object can be open
 * at any point in time
 *
 * Therefore, if the reading of one SpicaResultSet object is interleaved
 * with the reading of another, each must have been generated
 * by different SpicaStatement objects
 *
 * All statement execute methods implicitly close a statment's
 * current SpicaStatement object if an open one exists
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      October 22, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Statement.php 1498 2009-12-15 17:52:50Z pcdinh $
 */
interface SpicaStatement
{
    /**
     * The constant indicating that all SpicaResultSet objects that
     * have previously been kept open should be closed when calling getMoreResults()
     */
    const CLOSE_ALL_RESULTS    = 1;

    /**
     * The constant indicating that the current SpicaResultSet object
     * should be closed when calling getMoreResults()
     */
    const CLOSE_CURRENT_RESULT = 2;

    /**
     * The constant indicating that the current SpicaResultSet object
     * should not be closed when calling getMoreResults()
     */
    const KEEP_CURRENT_RESULT  = 3;

    /**
     * Indicate that the command executed successfully but the number of rows affected is unknown.
     *
     * @var int
     */
    const SUCCESS_NO_INFO = 4;

    /**
     * Indicate that the command failed to execute successfully
     *
     * @var int
     */
    const EXECUTE_FAILED = 5;

    /**
     * Gets driver-specific connection object.
     *
     * @return SpicaMySQLConnection
     */
    public function getConnection();

    /**
     * Gets the native or C-level driver-specific database connection object
     *
     * @throws SpicaDatabaseException
     * @return mysqli A connection to a MySQL server
     */
    public function getNativeConnection();

    /**
     * Cancels this statement object if both the DBMS and driver support aborting an SQL statement
     *
     * @throws SpicaDatabaseException
     */
    public function cancel();

    /**
     * Gives the Spica database driver a hint as to the number of rows that should be fetched
     * from the database when more rows are needed for this SpicaResultSet object.
     *
     * @param int $rows
     */
    public function setFetchSize($rows);

    /**
     * Executes the given SQL statement, which returns a single <code>SpicaResultSet</code> object.
     *
     * @throws SpicaDatabaseException if a database access error occurs, this method is called on a
     *         closed <code>SpicaStatement</code> or the given SQL statement produces
     *         anything other than a single <code>SpicaResultSet</code> object
     * @param  sql an SQL statement to be sent to the database, typically a static SQL <code>SELECT</code> statement
     * @return SpicaResultSet A <code>SpicaResultSet</code> object that contains the data produced
     *         by the given query; never <code>null</code>
     */
    public function executeQuery($sql);

    /**
     * Executes the given SQL statement, which may be an <code>INSERT</code>,
     * <code>UPDATE</code>, or <code>DELETE</code> statement or an
     * SQL statement that returns nothing, such as an SQL DDL statement (CREATE, DROP)
     *
     * @throws SpicaDatabaseException if a database access error occurs,
     * this method is called on a closed <code>SpicaStatement</code> or the given
     *            SQL statement produces a <code>SpicaResultSet</code> object
     * @param string $sql an SQL Data Manipulation Language (DML) statement, such as <code>INSERT</code>,
     *        <code>UPDATE</code> or <code>DELETE</code>; or an SQL statement that returns nothing, such as a DDL statement	 *
     * @return int Either (1) the row count for SQL Data Manipulation Language (DML) statements
     *         or (2) 0 for SQL statements that return nothing
     */
    public function executeUpdate($sql);

    /**
     * Executes the given SQL statement, which may return multiple results.
     * In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts.
     *
     * Normally you can ignore this unless you are (1) executing a stored procedure that you know may
     * return multiple results or (2) you are dynamically executing an unknown SQL string.
     * <P>
     * The <code>execute</code> method executes an SQL statement and indicates the
     * form of the first result.  You must then use the methods <code>getResultSet</code> or <code>getUpdateCount</code>
     * to retrieve the result, and <code>getMoreResults</code> to move to any subsequent result(s).
     *
     * @see #getResultSet
     * @see #getUpdateCount
     * @see #getMoreResults
     * @throws SpicaDatabaseException if a database access error occurs or this method is called on a
     *                                       closed <code>SpicaStatement</code>
     * @param  string $sql any SQL statement
     * @return bool <code>true</code> if the first result is a <code>SpicaResultSet</code> object;
     *              <code>false</code> if it is an update count or there are no results
     */
    public function execute($sql);

    /**
     * Adds the given SQL command to the current list of commmands for this Statement object
     *
     * @var string $sql
     */
    public function addBatch($sql);

    /**
     * Submits a batch of commands to the database for execution
     *
     * @return array An array of update counts containing one element for each command
     *         in the batch. The array is ordered according to the order in
     *         which commands were inserted into the batch
     *
     * @throws SpicaDatabaseException if a database-access error occurs, or the driver does not support batch statements
     * @throws SpicaDatabaseBatchUpdateException
     */
    public function executeBatch();

    /**
     * Creates a compiled statement for sending parameterized SQL statements to the database
     *
     * @param  string $sql
     * @return SpicaPreparedStatement
     */
    public function prepare($sql);

    /**
     * Releases this <code>SpicaStatement</code> object's database
     * and Connection resources immediately instead of waiting for
     * this to happen when it is automatically closed.
     * It is generally good practice to release resources as soon as
     * you are finished with them to avoid tying up database
     * resources.
     * <P>
     * Calling the method <code>close</code> on a <code>SpicaStatement</code>
     * object that is already closed has no effect.
     * <P>
     * <B>Note:</B>When a <code>SpicaStatement</code> object is
     * closed, its current <code>SpicaResultSet</code> object, if one exists, is
     * also closed.
     *
     * @throws SpicaDatabaseException if a database access error occurs
     */
    public function close();

    /**
     * Retrieves the current result as a <code>SpicaResultSet</code> object.
     * This method should be called only once per result.
     *
     * @return SpicaResultSet The current result as a <code>SpicaResultSet</code>
     * @throws SpicaDatabaseException if a database access error occurs or
     *         this method is called on a closed <code>SpicaStatement</code>
     * @see #execute
     */
    public function getResultSet();

    /**
     * Moves the specified statement to the next result set object and returns whether there is a next result set object.
     *
     * Note that the getMoreResults() method should close any SpicaResultSet it previously opened
     *
     * @param  int $current
     * @return bool true if there are more result set objects. false if there are no more result set objects or if it is an update count.
     */
    public function getMoreResults($current = Statement::CLOSE_CURRENT_RESULT);

    /**
     * Gets next result set in the batch
     *
     * @return SpicaMySQLResultSet|null
     */
    public function getNextResultSet();

    /**
     * Gets unique id of this connection
     *
     * @return int
     */
    public function getConnectionId();

    /**
     * Fetches result set of multiple rows returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @param  int    $caseMode The value may be one of: null,
     *                          ResultSet::IDENTIFIER_LOWERCASE,
     *                          ResultSet::IDENTIFIER_UPPERCASE,
     *                          ResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchAll($sql, $caseMode = null);

    /**
     * Fetches the first column of the first row in a result set as a string
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return string|null Null value is returned when no row is found
     */
    public function fetchOne($sql);

    /**
     * Fetches a single column from a result set as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return array An empty array is returned when there is no row found
     */
    public function fetchColumn($sql);

    /**
     * Fetches a single row from result set returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @param  int    $caseMode The value may be one of: null,
     *                          ResultSet::IDENTIFIER_LOWERCASE,
     *                          ResultSet::IDENTIFIER_UPPERCASE,
     *                          ResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchRow($sql, $caseMode = null);

    /**
     * Retrieves certain set of rows for page navigation.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql           The SQL command
     * @param  int    $pageNumber    The page sequence number
     * @param  int    $recordPerPage The number of records that will be displayed on each page
     * @param  int    $caseMode The value may be one of: null,
     *                          ResultSet::IDENTIFIER_LOWERCASE,
     *                          ResultSet::IDENTIFIER_UPPERCASE,
     *                          ResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchPage($sql, $pageNumber = 1, $recordPerPage = 20, $caseMode = null);

    /**
     * Retrieves the certain number of rows counted on a given row sequence number.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql  The SQL command
     * @param  int    $from The beginning row sequence number that will be retrieved, starting with 0
     * @param  int    $to   The last row sequence number that will be retrieved
     * @param  int    $caseMode The value may be one of: null,
     *                          ResultSet::IDENTIFIER_LOWERCASE,
     *                          ResultSet::IDENTIFIER_UPPERCASE,
     *                          ResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchRange($sql, $from = 0, $to = 20, $caseMode = null);

    /**
     * Inserts a single or multiple records into a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array $data An array of user input with specification: [field => value]
     * @return int An integer greater than zero indicates the number of rows affected or retrieved
     */
    public function insert($tableName, $data);

    /**
     * Updates data in a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @param  string $where Conditional phrase for UPDATE SQL command
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function update($tableName, $data, $where = array());

    /**
     * Replaces an entire existing record or multiple existing records based on
     * an unique index (with single or multiple keys) or a primary key by new one(s)
     * or insert new records if there is no existing match(es)
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @param  array  $options Optional arguments
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function replace($tableName, $data, $options = array());

    /**
     * Inserts an entire existing record based on an unique index (with single or multiple keys)
     * or a primary key or any kind of conditions by new one(s) or update the current matching one(s).
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @param  array  $options Optional arguments
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function merge($tableName, $data, $options = array());

    /**
     * Deletes a single row or multiple rows in a table, using an array as user input conditions
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array|string $where Conditional phrase for DELETE SQL command.
     *                      As an arrray: [field => condition]. E.x : array('id' => null, 'name' = 'LIKE %pcdinh%')
     *                      As a string: "id = 3 AND username = 'pcdinh'" (without WHERE)
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an DELETE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function delete($tableName, $where = array());

    /**
     * Gets the last query
     *
     * @return string
     */
    public function getLastQuery();
}

/**
 * The object used for executing a big trunk of SQL statements and obtaining the results produced by it
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.3
 * @since      December 13, 2009
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Statement.php 1498 2009-12-15 17:52:50Z pcdinh $
 */
interface SpicaMultipleQueryStatement
{
    /**
     * Sets a delimeter that is used to define CREATE FUNCTION and CREATE PROCEDURE
     *
     * @param string $delimeter
     */
    public function setDelimeter($delimeter);

    /**
     * Executes multiple queries separated by a delimiter such as ";;" (default) or "$$".
     *
     * The delimiter has to be changed when using command line interface to MySQL.
     *
     * You have more problem cases than just semicolons within strings.
     *  + Script builtin commands that cannot be executed by mysql_query(), like USE.
     *  + Statements that are not terminated with a semicolon, like DELIMITER.
     *  + Statements that contain semicolons, but not inside quotes, like CREATE PROCEDURE.
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @return bool
     */
    public function execute($sql);

    /**
     * Moves to this SpicaMySQLMultipleStatement object's next result, deals with any current SpicaMySQLResultSet object(s)
     * according to the instructions specified by the given flag, and returns true if the next result
     * is a SpicaMySQLResultSet object
     *
     * There are no more results when the following is true:
     * @example
     *   // $stmt is a SpicaMultipleStatement object
     *   (($stmt->getMoreResults() == false) && ($stmt->getUpdateCount() == false))
     *   while ($stmt->getMoreResults()) {
     *       $rs = $stmt->getResultSet();
     *   }
     *
     * @param  int $current
     * @return bool
     */
    public function getMoreResults($current = SpicaStatement::CLOSE_CURRENT_RESULT);

    /**
     * Gets next result set in the batch
     *
     * @return SpicaMySQLResultSet
     */
    public function getNextResultSet();

    /**
     * Gets internals queries created by this object from user-defined queries.
     *
     * @return array|null
     */
    public function getInternalQueries();

    /**
     * Gets the query that produces the current result set.
     */
    public function getCurrentQuery();
}

?>